package com.example.hibernate.demo.repository;

import com.example.hibernate.demo.entity.Employee;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.List;
import java.util.Optional;

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
    
    Optional<Employee> findByEmail(String email);
    
    List<Employee> findByDepartmentId(Long departmentId);
    
    @Query("SELECT e FROM Employee e WHERE e.firstName LIKE %:name% OR e.lastName LIKE %:name%")
    List<Employee> findByName(@Param("name") String name);
    
    @Query("SELECT e FROM Employee e WHERE e.salary BETWEEN :minSalary AND :maxSalary")
    List<Employee> findBySalaryRange(BigDecimal minSalary, BigDecimal maxSalary);
    
    @Query("SELECT e FROM Employee e JOIN FETCH e.department")
    List<Employee> findAllWithDepartment();
    
    @Query("SELECT e FROM Employee e JOIN FETCH e.projects WHERE e.id = :id")
    Optional<Employee> findByIdWithProjects(@Param("id") Long id);
    
    @Query(value = "SELECT e.* FROM employees e JOIN departments d ON e.department_id = d.id WHERE d.name = :departmentName", 
           nativeQuery = true)
    List<Employee> findByDepartmentName(@Param("departmentName") String departmentName);
    
    Page<Employee> findByStatus(Employee.EmployeeStatus status, Pageable pageable);
}